



ALTER      proc sp_get_lot_numbers
@process_step_id int,
@location_Id int
as
	declare @lot_Id int
	declare @lot_number varchar(50)
	declare @status varchar(20)
	declare @results Table(LotID varchar(20),LotNumber varchar(50),Status varchar(50))
	insert into @results (LotID,LotNumber,Status) values (0,'---Lots In Finished Goods---','')	
	declare lotCursor CURSOR FOR
		select lot_id,lot_number,lot_current_status from lot_table where ProcessStep_ID = @process_step_id and Location_ID = @location_Id and  Lot_Current_Status = 'Running'
	open lotCursor 
		fetch next from lotCursor into @lot_Id,@lot_number,@status
		while @@fetch_status = 0
		begin
			insert into @results (LotID,LotNumber,Status) values (@lot_Id,@lot_number,@status)
			print 'First Insert '+convert(varchar,@lot_number)
			set @lot_id = null
			set @lot_number = null
			set @status = null 
			fetch next from lotCursor into @lot_Id,@lot_number,@status
		end
		close lotCursor
		deallocate lotCursor
		insert into @results (LotID,LotNumber,Status) values (0,'---Lots In Test---','')	
		declare @prevProcessStepID int
		declare @temp_type_name varchar(20)
		
		select @prevProcessStepID = PSTT.ProcessStep_ID from Process_Step_Traverse_Table PSTT, process_step PS where Next_ProcessStep_ID = @process_step_id
		select @temp_type_name = PS_Type_Name from process_step where ProcessStep_ID = @prevProcessStepID			
		print 'First '+ @temp_type_name
		
		while (@temp_type_name <> 'Test')
			begin
			print 'In the while'
				declare @tempProcessStepID int
				declare @type_name varchar (20)
				declare @dummy varchar (20)
				select @tempProcessStepID= PSTT.ProcessStep_ID from Process_Step_Traverse_Table PSTT, process_step PS where PSTT.Next_ProcessStep_ID = @prevProcessStepID
				select @type_name = PS_Type_Name from process_step where ProcessStep_ID = @tempProcessStepID
				print 'Prev process Id '+convert(varchar,@prevProcessStepID)
				print ' name::'+ @type_name
				if @type_name <> 'TEST'
					begin
					print 'This is check'
					set @prevProcessStepID = @tempProcessStepID
					end
				else
					begin
					print 'This is the check '+ @type_name
					set @prevProcessStepID = @tempProcessStepID
					print 'Prev process Id '+convert(varchar,@prevProcessStepID)
					break
					end			
			end
                
		declare lotShippingCursor CURSOR FOR
		select lot.lot_id,lot.lot_number,lot.Lot_Current_Status from lot_table lot,Process_Step PS where PS.ProcessStep_ID = @prevProcessStepID and  PS.ProcessStep_ID = lot.ProcessStep_ID and  lot.Lot_Current_Status= 'Running'
		open lotShippingCursor 
			fetch next from lotShippingCursor into @lot_Id,@lot_number,@status
			while @@fetch_status = 0
			begin
				insert into @results (LotID,LotNumber,Status) values (@lot_Id,@lot_number,@status)
				print convert(varchar,@lot_number)
				set @lot_id = null
				set @lot_number = null
				set @status = null 
				fetch next from lotShippingCursor into @lot_Id,@lot_number,@status
			end
		close lotShippingCursor
		deallocate lotShippingCursor
		
		select * from @results





